﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using MockPrjTeam2.Biz;

namespace MockPrjTeam2.Common
{
    public class SqlHelper: IDisposable
    {
        private readonly SqlConnection _connection;
        private int _result;
        private SqlDataAdapter _dataAdapter;
        private DataSet _dataSet;
        private SqlDataReader _dataReader;
        private DataTable _dataTable;
        private MyCustomException _myException = null;

        public SqlHelper()
        {
            this._dataAdapter = new SqlDataAdapter();
            this._connection = new SqlConnection(AppConfig.ConnectionString());
        }

        public DataTable ExecuteSelectQuery(string query)
        {
            var selectCommand = new SqlCommand();
            this._dataTable = new DataTable();
            var ds = new DataSet();
            try
            {
                selectCommand.Connection = AppConfig.OpenConnection();
                selectCommand.CommandType = CommandType.Text;
                selectCommand.CommandText = query;
                this._dataAdapter = new SqlDataAdapter(selectCommand);
                this._dataAdapter.Fill(ds);
                this._dataTable = ds.Tables[0];
            }
            catch (Exception ex)
            {
                _myException = new MyCustomException("Common.SqlHelper.ExecuteSelectQuery", "Unexpected error when Executing Retrieve command", ex);
            }
            finally
            {
                this._connection.Close();
                if (_myException != null)
                {
                    throw _myException;
                }
            }

            return this._dataTable;
        }

        public DataTable ExecuteSelectQuery(string query, SqlParameter[] sqlParameters)
        {
            var selectCommand = new SqlCommand();
            this._dataTable = new DataTable();
            this._dataSet = new DataSet();
            try
            {
                selectCommand.Connection = AppConfig.OpenConnection();
                selectCommand.CommandType = CommandType.Text;
                selectCommand.CommandText = query;

                if (sqlParameters != null)
                {
                    selectCommand.Parameters.AddRange(sqlParameters);
                }

                this._dataAdapter = new SqlDataAdapter(selectCommand);
                this._dataAdapter.Fill(this._dataSet);
                this._dataTable = this._dataSet.Tables[0];
            }
            catch (Exception ex)
            {
                _myException = new MyCustomException("Common.SqlHelper.ExecuteSelectQuery", "Unexpected error when Executing Retrieve command", ex);
            }
            finally
            {
                this._connection.Close();

                //if (_myException != null)
                //{
                //    throw _myException;
                //}
            }

            return this._dataTable;
        }

        public IDataReader ExecuteReader(string query, SqlParameter[] sqlParameters)
        {
            IDataReader result = null;
            try
            {
                
                var conn = new SqlConnection(AppConfig.ConnectionString());
                conn.Open();

                var cmd = new SqlCommand(query, conn) { CommandType = CommandType.Text };
                if (sqlParameters != null) cmd.Parameters.AddRange(sqlParameters);

                result = cmd.ExecuteReader(CommandBehavior.CloseConnection);                
                
            }
            catch(Exception ex)
            {
                Console.Write(ex.StackTrace);
                if (result != null)
                {
                    result.Close();
                }
                throw;
            }
            return result;
        }

        public int ExecuteScalar(string query, SqlParameter[] sqlParameters)
        {
            var selectCommand = new SqlCommand();
            try
            {
                selectCommand.Connection = AppConfig.OpenConnection();
                selectCommand.CommandType = CommandType.Text;
                selectCommand.CommandText = query;

                if (sqlParameters != null)
                {
                    selectCommand.Parameters.AddRange(sqlParameters);
                }

                this._dataAdapter.SelectCommand = selectCommand;
                this._result = Convert.ToInt32(selectCommand.ExecuteScalar());
            }
            catch
            {
            }
            finally
            {
                this._connection.Close();
            }

            return this._result;
        }

        public int ExecuteScalar(string query)
        {
            this._dataAdapter = new SqlDataAdapter();
            var selectCommand = new SqlCommand();

            try
            {
                selectCommand.Connection = AppConfig.OpenConnection();
                selectCommand.CommandType = CommandType.Text;
                selectCommand.CommandText = query;
                this._dataAdapter.SelectCommand = selectCommand;
                this._result = Convert.ToInt32(selectCommand.ExecuteScalar());
            }
            catch (Exception ex)
            {
            }
            finally
            {
                this._connection.Close();
            }

            return this._result;
        }

        public bool ExecuteInsertQuery(string query, SqlParameter[] sqlParameters)
        {
            var insertCommand = new SqlCommand();
            try
            {
                insertCommand.Connection = AppConfig.OpenConnection();
                insertCommand.CommandType = CommandType.Text;
                insertCommand.CommandText = query;

                if (sqlParameters != null)
                {
                    insertCommand.Parameters.AddRange(sqlParameters);
                }

                insertCommand.ExecuteNonQuery();
                this._dataAdapter.InsertCommand = insertCommand;

                return true;
            }
            catch(Exception ex)
            {
                return false;
            }
            finally
            {
                this._connection.Close();
            }
        }

        public bool ExecuteUpdateQuery(string query, SqlParameter[] sqlParameters)
        {
            SqlConnection.ClearAllPools();
            var updateCommand = new SqlCommand();
            try
            {
                updateCommand.Connection = AppConfig.OpenConnection();
                updateCommand.CommandType = CommandType.Text;
                updateCommand.CommandText = query;

                if (sqlParameters != null)
                {
                    updateCommand.Parameters.AddRange(sqlParameters);
                }

                updateCommand.ExecuteNonQuery();
                this._dataAdapter.UpdateCommand = updateCommand;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                this._connection.Close();
            }

            return true;
        }

        public bool ExecuteDeleteQuery(string query, SqlParameter[] sqlParameters)
        {
            var deleteCommand = new SqlCommand();
            try
            {
                deleteCommand.Connection = AppConfig.OpenConnection();
                deleteCommand.CommandType = CommandType.Text;
                deleteCommand.CommandText = query;

                if (sqlParameters != null)
                {
                    deleteCommand.Parameters.AddRange(sqlParameters);
                }

                deleteCommand.ExecuteNonQuery();
                this._dataAdapter.DeleteCommand = deleteCommand;
            }
            catch
            {
                return false;
            }
            finally
            {
                this._connection.Close();
            }

            return true;
        }

        #region IDisposable Members
        public void Dispose()
        {
            
        }
        #endregion
    }
}